package com.cy.ftp;

import com.jcraft.jsch.*;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.net.ftp.FTP;
import org.apache.commons.net.ftp.FTPClient;
import org.apache.commons.net.ftp.FTPFile;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Objects;
import java.util.Properties;
import java.util.Vector;

@Component
@Slf4j
public class ScheduledTasks {


    private HikariDataSource dataSource;

    @Value("${ftp.server}")
    private String server;

    @Value("${ftp.port}")
    private int port;

    @Value("${ftp.username}")
    private String username;

    @Value("${ftp.password}")
    private String password;


    public ScheduledTasks(@Value("${database.username}") String username,
                          @Value("${database.password}") String password,
                          @Value("${database.url}") String url
    ) {

        HikariConfig config = new HikariConfig();
        config.setUsername(username);
        config.setJdbcUrl(url);
        config.setPassword(password);

        log.info("MySQL 用户：" + username);
        log.info("MySQL url：" + url);
        log.info("MySQL 密码：" + password);
        // 配置最大连接数
        config.setMaximumPoolSize(10);
        // 配置最小空闲连接数
        config.setMinimumIdle(5);
        // 配置连接超时时间
        config.setConnectionTimeout(30000);
        // 配置空闲连接超时时间
        config.setIdleTimeout(600000);
        this.dataSource = new HikariDataSource(config);
    }

//    public ScheduledTasks() {
//        // 配置 HikariCP 连接池
//        HikariConfig config = new HikariConfig();
//        config.setUsername(username);
//        config.setJdbcUrl(url);
//        config.setPassword(password);
//        // 配置最大连接数
//        config.setMaximumPoolSize(10);
//        // 配置最小空闲连接数
//        config.setMinimumIdle(5);
//        // 配置连接超时时间
//        config.setConnectionTimeout(30000);
//        // 配置空闲连接超时时间
//        config.setIdleTimeout(600000);
//        this.dataSource = new HikariDataSource(config);
//    }

    @Scheduled(fixedDelay = 10 * 1000)
    public void performTask() throws SQLException {
        log.info(".");
        try (Connection connection = dataSource.getConnection()) {

            // 获取数据库的元数据
            DatabaseMetaData metaData = connection.getMetaData();

            // 打印数据库的URL
            System.out.println("Database URL: " + metaData.getURL());

            // 打印数据库的用户
            System.out.println("Database User: " + metaData.getUserName());

            // 打印数据库的驱动名称和版本
            System.out.println("Driver Name: " + metaData.getDriverName());
            System.out.println("Driver Version: " + metaData.getDriverVersion());

            // 打印数据库的产品名称和版本
            System.out.println("Database Product Name: " + metaData.getDatabaseProductName());
            System.out.println("Database Product Version: " + metaData.getDatabaseProductVersion());

            Statement statement = connection.createStatement();

            if (Objects.equals(port, 22)) {

                // 使用JSch创建SFTP客户端
                JSch jsch = new JSch();
                Session session = jsch.getSession(username, server, port);
                session.setPassword(password);

                // 避免首次连接时的主机密钥检查
                Properties config = new Properties();
                config.put("StrictHostKeyChecking", "no");
                session.setConfig(config);

                session.connect();
                ChannelSftp sftpChannel = (ChannelSftp) session.openChannel("sftp");
                sftpChannel.connect();

                // 列出SFTP目录中的文件
                Vector<ChannelSftp.LsEntry> files = sftpChannel.ls(".");

                for (ChannelSftp.LsEntry entry : files) {
                    if (!entry.getAttrs().isDir() && entry.getFilename().endsWith(".sql")) {
                        String fileName = entry.getFilename();
                        try (InputStream inputStream = sftpChannel.get(fileName);
                             BufferedReader br = new BufferedReader(new InputStreamReader(inputStream))) {

                            String line;
                            StringBuilder lineBuilder = new StringBuilder();
                            while ((line = br.readLine()) != null) {
                                lineBuilder.append(line);
                                while (!lineBuilder.toString().endsWith("@@@@@@;")) {
                                    line = br.readLine();
                                    if (line != null) {
                                        lineBuilder.append(System.lineSeparator()).append(line);
                                    } else {
                                        break;
                                    }
                                }
                                // 处理以分号结尾的完整内容
                                System.out.println("sql:" + lineBuilder.toString());
                                doExcuteSql(lineBuilder.toString().replace("@@@@@@;", ";"), statement);
                                lineBuilder.setLength(0); // 清空 StringBuilder
                            }

                            // 删除已处理的文件
                            sftpChannel.rm(fileName);
                            System.out.println("\n文件处理成功并已删除。");
                        }
                    }
                }

                if (files.isEmpty()) {
                    System.out.println("没有需要执行的SQL");
                }

                sftpChannel.disconnect();
                session.disconnect();


            } else {


                FTPClient ftpClient = new FTPClient();
                ftpClient.connect(server, port);
                ftpClient.login(username, password);
                ftpClient.enterLocalPassiveMode();
                ftpClient.setFileType(FTP.BINARY_FILE_TYPE);

                FTPFile[] files = ftpClient.listFiles();

                for (FTPFile file : files) {
                    if (file.getName().endsWith(".sql")) {
                        InputStream inputStream = ftpClient.retrieveFileStream(file.getName());
                        if (inputStream != null) {
                            BufferedReader br = new BufferedReader(new InputStreamReader(inputStream));
                            String line;
                            StringBuilder lineBuilder = new StringBuilder();
                            while ((line = br.readLine()) != null) {
                                lineBuilder.append(line);
                                while (!lineBuilder.toString().endsWith("@@@@@@;")) {
                                    line = br.readLine();
                                    if (line != null) {
                                        lineBuilder.append(System.lineSeparator()).append(line);
                                    } else {
                                        break;
                                    }
                                }
                                // 处理以分号结尾的完整内容
                                System.out.println("sql:" + lineBuilder.toString());
                                doExcuteSql(lineBuilder.toString().replace("@@@@@@;", ";"), statement);
                                lineBuilder.setLength(0); // 清空 StringBuilder
                                // 准备下一轮循环
                            }
                            br.close();
                            // 确保文件传输完成
                            boolean success = ftpClient.completePendingCommand();
                            if (success) {
                                System.out.println("\n文件读取成功。");
                            } else {
                                System.out.println("\n文件读取失败。");
                            }
                        }
                        ftpClient.deleteFile(file.getName());
                    }
                }
                if (files.length == 0) {
                    System.out.println("没有需要执行的SQL");
                }
                ftpClient.logout();
                ftpClient.disconnect();

            }
        } catch (IOException e) {
            e.printStackTrace();
        } catch (JSchException e) {
            throw new RuntimeException(e);
        } catch (SftpException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 抽象出来的方法，执行每一句sql
     *
     * @param line
     */
    public void doExcuteSql(String line, Statement statement) {


        // 每一行都是一句 sql
        if (!line.trim().isEmpty()) {
            line = line.replace("AND `job_data` = []", "");
            try {

                log.info(line);
                statement.execute(line);
                log.info("执行成功");

            } catch (Exception e) {
                if (e instanceof java.sql.SQLIntegrityConstraintViolationException) {
                    log.info("主键重复");
                } else {
                    log.error("sql执行失败：{},失败原因：{}", line, e.getMessage());
                }
            }
        }
    }

}
